﻿using System;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Security.Cryptography;

using SocialFilter.model;

namespace SocialFilter.controller
{
	static class DataController
	{
		private static MySqlConnection connection;
		private static string tokenKey = "";
		
		public static string TokenKey
		{
			get { return tokenKey; }
			set { tokenKey = value; }
		}
		
		// connect to DB using default port
		public static bool open( string _server, string _username, string _password, string _database )
		{
			return open( _server, _username, _password, _database, "3306");
		}

		// connect to DB
		public static bool open( string _server, string _username, string _password, string _database, string _port )
		{
			if ( connection != null ) // this controller is already connected, close it first
			{
				Console.WriteLine("This controller is already connected, close it before trying to open a new connection with it.");
				return false;
			}
			
			StringBuilder connectionString = new StringBuilder();
			connectionString.Append("server=");
			connectionString.Append(_server);
			connectionString.Append(";user=");
			connectionString.Append(_username);
			connectionString.Append(";password=");
			connectionString.Append(_password);
			connectionString.Append(";database=");
			connectionString.Append(_database);
			connectionString.Append(";port=");
			connectionString.Append(_port);
			connectionString.Append(";");
			
			connection = new MySqlConnection(connectionString.ToString());
			
			try
			{
				connection.Open();
				return true;
			}
			catch (Exception e)
			{
				Console.WriteLine("An error occurred while connecting: " + e.Message);
				return false;	
			}
		}

		// close db connection
		public static bool close()
		{
			try
			{
				connection.Close();
				return true;
			}
			catch (Exception e)
			{
				Console.WriteLine("An error occurred while closing the connection: " + e.Message);
				return false;
			}
		}
		
		// data access functions********************************************************************************************************
		
		//GetOutdatedFeeds - returns a list of outdated feeds, and info necessary to query the sources
		//feed id, feed name, url, account name, token, site name
		public static List<Feed> GetOutdatedFeeds()
		{
		    List<Feed> ret;
		    string tokenKey = ConfigurationController.GetConfiguration("token_key");
			tokenKey.Replace("\"", "\\\"");		 
		    string feedTimeout = SanitizeInput(ConfigurationController.GetConfiguration("feed_timeout"));
		    string query = "SELECT feed.feedid, feed.name, feed.url, site.name, user.account_name, CAST(AES_DECRYPT(user.token, \"" + tokenKey  + "\") AS CHAR) FROM feed, site, user WHERE deleted=FALSE AND ADDTIME(feed.last_updated, '" + feedTimeout + "') < NOW() AND feed.user_userid = user.userid AND user.site_siteid = site.siteid ORDER BY feed.feedid";
		    MySqlCommand command = new MySqlCommand(query, connection);
		    MySqlDataReader reader = command.ExecuteReader();
		    
		    ret = ExtractFeedsFromReader(reader);
		    reader.Close();
		    
		    // Update feeds' timestamps
		    UpdateFeedTimestamps(ret);
		    
		    return ret;
		}
		
		//GetFeedFilters - given a feed id, it retrieves all filters associated with that feed
		public static List<Filter> GetFeedFilters( string _feedId )
		{
			// get feed filters
			string feedId = SanitizeInput(_feedId);
			string query = "SELECT filter_string FROM socialfilter.filter WHERE feed_feedid=" + feedId + ";";
			MySqlCommand command = new MySqlCommand(query, connection);
			MySqlDataReader reader = command.ExecuteReader();
			List<Filter> filters = ExtractFiltersFromReader(reader);
			reader.Close();
			return filters;
		}
		
		//GetRequestedFeeds - returns a list of requested feeds, and info necessary to query the sources
		public static List<Feed> GetRequestedFeeds()
		{
			List<Feed> ret = new List<Feed>();
			// lock access to requested table
			List<string> lockedTables = new List<string>();
			lockedTables.Add("feed_update_requests");
			LockTables(lockedTables);
			// get requested feeds
			string query = "SELECT feed_feedid FROM socialfilter.feed_update_requests;";
			MySqlCommand command = new MySqlCommand(query, connection);
			MySqlDataReader reader = command.ExecuteReader();
			List<string> feedIds = new List<string>();
			while ( reader.Read() )
			{
				feedIds.Add(reader.GetString(0));
			}
			reader.Close();
			
			// empty requests table
			query = "DELETE FROM socialfilter.feed_update_requests;";
			command = new MySqlCommand(query, connection);
			command.ExecuteNonQuery();
			UnlockTables();
			
			if ( feedIds.Count > 0 )
			{
				// generate feedid query string
				string feedIdQueryPortion = " AND " + GenerateFeedIdQueryPortion(feedIds);
				string tokenKey = ConfigurationController.GetConfiguration("token_key");
				
				// now get information on the id's AFTERWARDS so we don't have to lock the user and feed tables too
				query = "SELECT feedid, feed.name, url, site.name, account_name, CAST(AES_DECRYPT(user.token, '" + tokenKey + "') AS CHAR) FROM socialfilter.feed, socialfilter.site, socialfilter.user WHERE deleted=FALSE AND user_userid=userid AND site_siteid=siteid" + feedIdQueryPortion;
				command = new MySqlCommand(query, connection);
				reader = command.ExecuteReader();
				
				ret = ExtractFeedsFromReader(reader);
				
				reader.Close();

				// Update feeds' timestamps
				UpdateFeedTimestamps(ret);
			}

			return ret;
		}

		//GetDeletedFeeds - returns a list of feeds marked for deletion
		public static List<Feed> GetDeletedFeeds()
		{
			List<Feed> ret = new List<Feed>();
			// get deleted feeds
			string query = "SELECT feedid, url FROM socialfilter.feed WHERE deleted=TRUE;";
			MySqlCommand command = new MySqlCommand(query, connection);
			MySqlDataReader reader = command.ExecuteReader();
			List<string> feedIds = new List<string>();
			Feed f;
			while (reader.Read())
			{
				f = new Feed();
				f.FeedId= reader.GetString(0);
				f.Url = reader.GetString(1);
				ret.Add(f);
			}
			reader.Close();

			return ret;
		}
		
		//Delete feeds with ids matching any in the given list
		public static void DeleteFeeds( List<string> _feedIds )
		{
			SanitizeInputs(_feedIds);
			
			// generate feed id query portion
			string feedIdQueryPortion = ";";
			if ( _feedIds.Count > 0 )
			{
				feedIdQueryPortion = " OR " + GenerateFeedIdQueryPortion(_feedIds) + ";";
			}
			string query = "DELETE FROM socialfilter.feed WHERE feedid=NULL" + feedIdQueryPortion;
			MySqlCommand command = new MySqlCommand(query, connection);
			command.ExecuteNonQuery();
		}
		
		//GetFeedUrl - get the url for a given feed
		public static string GetFeedUrl( string _feedId )
		{
			string feedId = SanitizeInput(_feedId);
			string query = "SELECT url FROM socialfilter.feed WHERE feedid=" + feedId + ";";
			MySqlCommand command = new MySqlCommand(query, connection);
			return (string)command.ExecuteScalar();
		}
		
		// Data control functions****************************************************************************************************************
		
		public static void SanitizeInputs( List<string> _inputs )
		{
			for ( int i = 0; i < _inputs.Count; ++i )
			{
				_inputs[i] = SanitizeInput(_inputs[i]);
			}
		}

		// Filter out bad input using regex
		public static string SanitizeInput( string _input )
		{
			return Regex.Replace( _input, "[^- _a-zA-Z0-9.@]", "", RegexOptions.None);
		}

		//assume feed id's are already sanitized
		private static string GenerateFeedIdQueryPortion(List<string> _feedIds)
		{
			if ( _feedIds.Count == 0 )
			{
				return "";
			}
			return "(feedId=" + string.Join(" OR feedid=", _feedIds.ToArray()) + ")";
		}

		// feed id, feed name, feed url, site name, account name, decrypted token
		private static List<Feed> ExtractFeedsFromReader(MySqlDataReader _reader)
		{
			List<Feed> ret = new List<Feed>();
			Dictionary<string, Feed> feeds = new Dictionary<string, Feed>();
			string feedId;
			while (_reader.Read())
			{
				feedId = _reader.GetString(0);
				if (!feeds.ContainsKey(feedId))
				{
					Feed f = new Feed();
					f.FeedId = feedId;
					f.FeedName = _reader.GetString(1);
					f.Url = _reader.GetString(2);
					feeds.Add(feedId, f);
				}

				Account a = new Account();
				a.AccountName = _reader.GetString(4);
				a.SiteName = _reader.GetString(3);
				a.Token = _reader.GetString(5);
				feeds[feedId].AddAccount(a);
			}
			foreach (Feed f in feeds.Values)
			{
				ret.Add(f);
			}
			return ret;
		}

		// filter string
		private static List<Filter> ExtractFiltersFromReader(MySqlDataReader _reader)
		{
			List<Filter> ret = new List<Filter>();
			while (_reader.Read())
			{
				ret.Add(new Filter(_reader.GetString(0)));
			}
			return ret;
		}

		//UpdateFeedTimestamps - update the timestamp on the feed indicating when it was last updated (set to NOW())
		private static void UpdateFeedTimestamps(List<Feed> _feeds)
		{
			// update feed timestamps
			List<string> feedIds = new List<string>();
			foreach (Feed feed in _feeds)
			{
				// sanitize feed id
				feedIds.Add(SanitizeInput(feed.FeedId));
			}
			// generate feed id portion
			string feedIdQueryPortion = "";
			if ( feedIds.Count > 0)
			{
				feedIdQueryPortion = " OR " + GenerateFeedIdQueryPortion(feedIds) + ";";
			}

			string query = "UPDATE socialfilter.feed SET last_updated=NOW() WHERE feedId=NULL" + feedIdQueryPortion;
			MySqlCommand command = new MySqlCommand(query, connection);
			command.ExecuteNonQuery();
		}
		
		// Lock access to requested tables
		private static void LockTables( List<string> _tableNames )
		{
			SanitizeInputs(_tableNames);
			string tableString = string.Join(" WRITE, ", _tableNames.ToArray());
			string query = "LOCK TABLES " + tableString + " WRITE;";
			MySqlCommand command = new MySqlCommand( query, connection );
			command.ExecuteNonQuery();
		}
		
		// Unlock all tables
		private static void UnlockTables()
		{
			string query = "UNLOCK TABLES;";
			MySqlCommand command = new MySqlCommand(query, connection);
			command.ExecuteNonQuery();
		}
	}
}
